﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GBMS.MODEL;

namespace KPIS.GERP.GBMS.BLL
{
    public sealed class D_BGM_ProjectOperation
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();
        //string strGHRMS_DB_Name = ConfigurationSettings.AppSettings["GHRMS.DB.Name"].ToString();

        public D_BGM_ProjectOperation()
        {

        }

        public bool ReturnExpense(string bgm_proj_oper_seq, string emp_seq, string bgm_source_seq)
        {
            bool bOK = false;
            string strSQL;
            DataTable dtb = null;
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_proj_oper_seq", bgm_proj_oper_seq);
                dbManager.AddParameters(2, "@emp_seq", emp_seq);
                dbManager.AddParameters(3, "@updated_when", DateTime.Now);
                dbManager.AddParameters(4, "@bgm_source_seq", bgm_source_seq);

                strSQL = "select * from (SELECT opex.bgm_proj_oper_exp_seq, opex.bgm_proj_exp_seq, pjex.bgm_source_seq"
                    + ", sum(coalesce(rpt.cal_amount, 0)) as amount"

                    + " FROM bgm_project_operation_expense opex"
                    + " LEFT JOIN bgm_project_expense pjex ON opex.bgm_proj_exp_seq = pjex.bgm_proj_exp_seq"
                    + " LEFT JOIN bgm_project_operation_report rpt ON opex.bgm_proj_oper_exp_seq = rpt.bgm_proj_oper_exp_seq"

                    + " WHERE rpt.record_status not like @delete_status"
                    + " and pjex.bgm_source_seq = @bgm_source_seq"
                    + (bgm_proj_oper_seq != null ? " and opex.bgm_proj_oper_seq = @bgm_proj_oper_seq" : "")
                    + " group by opex.bgm_proj_oper_exp_seq, opex.bgm_proj_exp_seq, pjex.bgm_source_seq) t"
                    + " order by t.bgm_source_seq, t.amount";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                if (dtb.Rows.Count > 0)
                {
                    strSQL = "update bgm_project_operation"
                        + " set record_status = 'C'"
                        + ", updated_by = @emp_seq"
                        + ", updated_when = @updated_when"
                        + " where bgm_proj_oper_seq = @bgm_proj_oper_seq";
                    dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                    for (int i = 0; i < dtb.Rows.Count; i++)
                    {
                        DataRow dr = dtb.Rows[i];
                        double dAmount = cn.ConvertNullDouble(dr["amount"]) ?? 0;

                        if (dAmount < 0 )
                        {
                            int nStartIndex = i;
                            DataRow drNext = dtb.Rows[nStartIndex + 1];
                            double dAmountNext = cn.ConvertNullDouble(drNext["amount"]) ?? 0;
                            dAmountNext = dAmountNext + dAmount;
                            dtb.Rows[nStartIndex + 1]["amount"] = dAmountNext;
                        }
                        else
                        {
                            strSQL = "insert into bgm_project_operation_report"
                                + " (bgm_proj_oper_exp_seq, used_amount, cal_amount, rpt_type, record_status, created_by, created_when)"
                                + " values"
                                + " (" + dr["bgm_proj_oper_exp_seq"].ToString()
                                + ", " + dr["amount"].ToString()
                                + ", -" + dr["amount"].ToString()
                                + ", 1"
                                + ", 'U'"
                                + ", @emp_seq"
                                + ", @updated_when"
                                + ")";
                            dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                            strSQL = "insert into bgm_project_report"
                                + " (bgm_proj_exp_seq, used_amount, cal_amount, rpt_type, record_status, created_by, created_when)"
                                + " values"
                                + " (" + dr["bgm_proj_exp_seq"].ToString()
                                + ", " + dr["amount"].ToString()
                                + ", " + dr["amount"].ToString()
                                + ", 8"
                                + ", 'U'"
                                + ", @emp_seq"
                                + ", @updated_when"
                                + ")";
                            dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                        }
                    }

                    bOK = true;
                }

                dbManager.CommitTransaction();
            }
            catch (Exception ex)
            {
                dbManager.RollBackTransaction();
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return bOK;
        }
    }
}
